package coollemon.dataBase;

import java.io.File;
import java.io.IOException;
import java.util.*;
import java.text.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import coollemon.kernel.Contact;
import coollemon.kernel.ContactManager;
import coollemon.kernel.NormalTag;
import coollemon.kernel.Pair;
import coollemon.kernel.RelationTag;
import coollemon.kernel.BirthDay;

;

public class XLS extends DataFormat {
	public static XLS xls = new XLS();

	private XLS() {
	};

	public ContactManager readFile(String filename) {
		// ContactManager conM = new ContactManager();
		ArrayList<Contact> contacts = new ArrayList<Contact>();
		try {
			Workbook book = Workbook.getWorkbook(new File(filename));
			// get a Sheet object.
			Sheet sheet = book.getSheet(0);
			// System.out.println(sheet.getRows());
			for (int i = 1; i < sheet.getRows(); i++) {
				// get content.
				Cell cell = null;

				cell = sheet.getCell(0, i);
				String name = cell.getContents();
				cell = sheet.getCell(1, i);
				String phoneNum = cell.getContents();
				cell = sheet.getCell(2, i);
				String fixedTel = cell.getContents();
				cell = sheet.getCell(3, i);
				String email = cell.getContents();
				cell = sheet.getCell(4, i);
				String qq = cell.getContents();
				cell = sheet.getCell(5, i);
				String nick = cell.getContents();

				cell = sheet.getCell(6, i);
				String sexStr = cell.getContents();
				int sex = 0;
				if (sexStr.equalsIgnoreCase("M"))
					sex = 1;
				else if (sexStr.equalsIgnoreCase("F"))
					sex = 2;
				else
					;

				cell = sheet.getCell(7, i);
				BirthDay birth = new BirthDay(cell.getContents());

				cell = sheet.getCell(8, i);
				String icon = cell.getContents();
				cell = sheet.getCell(9, i);
				String addr = cell.getContents();
				cell = sheet.getCell(10, i);
				String workplace = cell.getContents();
				cell = sheet.getCell(11, i);
				String zipCode = cell.getContents();

				cell = sheet.getCell(12, i);
				String homePage = cell.getContents();

				ArrayList<String> otherWay = new ArrayList<String>();
				cell = sheet.getCell(13, i);
				String others = cell.getContents();

				String regex = "([^;]+);*";
				Pattern pattern = Pattern.compile(regex);
				Matcher matcher = pattern.matcher(others);
				while (matcher.find()) {
					String str = matcher.group(1);
					otherWay.add(str);
				}

				ArrayList<NormalTag> normal = new ArrayList<NormalTag>();
				cell = sheet.getCell(14, i);
				String normals = cell.getContents();
				matcher = pattern.matcher(normals);
				while (matcher.find()) {
					String str = matcher.group(1);
					normal.add(NormalTag.createNormalTag(str));
				}

				Contact contact = Contact.createContact(name, phoneNum,
						fixedTel, email, qq, nick, sex, birth, icon, addr,
						workplace, zipCode, homePage, otherWay, normal, null);
				contacts.add(contact);

				cell = sheet.getCell(15, i);
				String relations = cell.getContents();
				regex = "(<([^;]+),([^;]+),([^;]+)>);*";
				pattern = Pattern.compile(regex);
				matcher = pattern.matcher(relations);
				while (matcher.find()) {
					String tag = matcher.group(2);
					String name1 = matcher.group(3);
					String name2 = matcher.group(4);

					RelationTag relationTag = RelationTag
							.createRelationTag(tag);

					ArrayList<Contact> B = new ArrayList<Contact>();
					for (int j = 0; j < contacts.size(); j++) {
						if (contacts.get(j).getName().equals(name2))
							B.add(contacts.get(j));
					}
					System.out.println(B.size());
					if (contact.getName().equals(name1)) {
						for (int k = 0; k < B.size(); k++) {
							ContactManager.addTagToContact(
									new Pair<Contact, Contact>(contact, B
											.get(k)), relationTag);
						}
					}

				}

				// contacts.add(contact);

				/*
				 * System.out.print(name + "\t" + phoneNum + "\t" + fixedTel +
				 * "\t" + email + "\t" + qq + "\t" + nick + "\t" + sex + "\t" +
				 * birth + "\t" + icon + "\t" + addr + "\t" + workplace + "\t" +
				 * zipCode + "\t" + homePage + "\t" + otherWay + "\t" + normal +
				 * "\t" + relation + "\n");
				 */

			}
			book.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return new ContactManager(contacts);
	}

	public boolean writeFile(ContactManager conM, String filename) {
		try {
			WritableWorkbook book = Workbook.createWorkbook(new File(filename));
			WritableSheet sheet = book.createSheet("Sheet_1", 0);
			// add title
			Label label = null;

			label = new Label(0, 0, "name");
			sheet.addCell(label);
			label = new Label(1, 0, "phoneNumber");
			sheet.addCell(label);
			label = new Label(2, 0, "fixedTel");
			sheet.addCell(label);
			label = new Label(3, 0, "email");
			sheet.addCell(label);
			label = new Label(4, 0, "qq");
			sheet.addCell(label);
			label = new Label(5, 0, "nick");
			sheet.addCell(label);
			label = new Label(6, 0, "sex");
			sheet.addCell(label);
			label = new Label(7, 0, "birthday");
			sheet.addCell(label);
			label = new Label(8, 0, "icon");
			sheet.addCell(label);
			label = new Label(9, 0, "address");
			sheet.addCell(label);
			label = new Label(10, 0, "workspace");
			sheet.addCell(label);
			label = new Label(11, 0, "zipCode");
			sheet.addCell(label);
			label = new Label(12, 0, "homePage");
			sheet.addCell(label);
			label = new Label(13, 0, "otherWay");
			sheet.addCell(label);
			label = new Label(14, 0, "normal");
			sheet.addCell(label);
			label = new Label(15, 0, "relation");
			sheet.addCell(label);

			// add contact
			ArrayList<Contact> contacts = conM.getContacts();
			for (int i = 0; i < contacts.size(); i++) {
				Contact contact = contacts.get(i);
				label = new Label(0, i + 1, contact.getName());
				sheet.addCell(label);
				label = new Label(1, i + 1, contact.getPhoneNumber());
				sheet.addCell(label);
				label = new Label(2, i + 1, contact.getFixedTel());
				sheet.addCell(label);
				label = new Label(3, i + 1, contact.getEmail());
				sheet.addCell(label);
				label = new Label(4, i + 1, contact.getQq());
				sheet.addCell(label);
				label = new Label(5, i + 1, contact.getNick());
				sheet.addCell(label);

				switch (contact.getSex()) {

				case 1:
					label = new Label(6, i + 1, "M");
					break;
				case 2:
					label = new Label(6, i + 1, "F");
					break;
				default:
					label = new Label(6, i + 1, "");
					break;
				}
				sheet.addCell(label);

				String birthday = contact.getBirthday().toString();
				label = new Label(7, i + 1, birthday);
				;
				sheet.addCell(label);

				label = new Label(8, i + 1, contact.getIcon());
				sheet.addCell(label);
				label = new Label(9, i + 1, contact.getAddress());
				sheet.addCell(label);
				label = new Label(10, i + 1, contact.getWorkplace());
				sheet.addCell(label);
				label = new Label(11, i + 1, contact.getZipCode());
				sheet.addCell(label);
				label = new Label(12, i + 1, contact.getHomepage());
				sheet.addCell(label);
				// add otherWays
				ArrayList<String> otherWays = contact.getOtherWay();
				String otherWayStr = "";
				if (otherWays.size() > 0) {
					for (int j = 0; j < otherWays.size() - 1; j++) {
						otherWayStr += otherWays.get(j);
						otherWayStr += ";";
					}
					otherWayStr += otherWays.get(otherWays.size() - 1);
				}
				label = new Label(13, i + 1, otherWayStr);
				sheet.addCell(label);
				// add NormalTag
				ArrayList<NormalTag> normals = contact.getNormal();
				String normalStr = "";
				if (normals.size() > 0) {
					for (int j = 0; j < normals.size() - 1; j++) {
						normalStr += normals.get(j).getName();
						normalStr += ";";
					}
					normalStr += normals.get(normals.size() - 1).getName();
				}
				label = new Label(14, i + 1, normalStr);
				sheet.addCell(label);

				// add relation
				ArrayList<RelationTag> relations = contact.getRelation();
				String relationStr = "";
				for (int j = 0; j < relations.size(); j++) {
					RelationTag relationTag = relations.get(j);
					ArrayList<Contact> cons = relationTag.getContacts(contact);
					for (int k = 0; k < cons.size(); k++) {
						relationStr = relationStr + "<" + relationTag.getName()
								+ "," + contact.getName() + ","
								+ cons.get(k).getName() + ">" + ";";
					}
				}
				if (!relationStr.isEmpty())
					relationStr = relationStr.substring(0,
							relationStr.length() - 1);
				label = new Label(15, i + 1, relationStr);
				sheet.addCell(label);

			}
			book.write();
			book.close();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	public static void main(String[] args) throws Exception {
		ContactManager conM = xls.readFile("./data/Test.xls");
		xls.writeFile(conM, "./data/Test_write.xls");
		
	}
}
